# coding=utf-8

from openpyxl import load_workbook
import datetime


def make_dict(file_name, sheet_name):
    wb = load_workbook(filename=file_name)
    ws = wb[sheet_name]

    colA = ws['A']
    row1 = ws[1]

    dictionary = dict()
    for province in colA:
        if province.value is not None:
            dictionary[province.value] = dict()
            for year in row1:
                if year.value is not None:
                    if type(year.value) is datetime.datetime:
                        dictionary[province.value][year.value.year] = ws[year.column + str(province.row)].value
                    elif type(year.value) is long:
                        dictionary[province.value][year.value] = ws[year.column + str(province.row)].value
                    else:
                        pass
    return dictionary


def match_province(debt_principal, province):
    return debt_principal.find(province) >= 0


def fill_column(ws, columns, columns_number, total_line_number, name, dictionary):
    this_column_number = len(columns_number) + 1
    columns_number[name] = this_column_number
    ws.cell(column=this_column_number, row=1, value=name)

    for line in range(1, total_line_number, 1):
        for province in dictionary.keys():
            if match_province(columns[columns_number[u'债务主体'] - 1][line].value, province):
                try:
                    ws.cell(column=this_column_number, row=line + 1,
                            value=dictionary[province][columns[columns_number[u'年份'] - 1][line].value])
                except KeyError:
                    pass

def fill_years(ws, cols, columns_number, total_line_number ):
    this_column_number = len(columns_number) + 1
    columns_number[u'年份'] = this_column_number
    ws.cell(column=this_column_number, row=1, value=u'年份')

    for i in range(1, total_line_number, 1):
        # print cols[column_number[u'证券简称']][i].value
        digitCount = 0
        newString = ""
        for character in cols[columns_number[u'证券简称'] - 1][i].value:
            if character.isdigit():
                digitCount += 1
                newString += character
            if digitCount == 2:
                break

        if len(newString) < 2:
            continue

        ws.cell(row=i + 1, column=this_column_number, value=int("20" + newString))
